﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmCorporateProductionApproval

    Dim FolderPath As String = FTPAddress + "Production Corporate"

    Sub FillPendingTransactions()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select SNo,ClientNo,ClientName,EndorsType,SavedUser,SavedDate " & _
                                      "From Policies Where Done=0 and Branch=N'" & Branch & "' Order By SavedDate", cnn)
            Dim Reader As SqlDataReader
            Dim X As Integer = 1

            Clear()
            Me.ListPendingTrans.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("SNo")
                Item.SubItems.Add(X)
                Item.SubItems.Add(Reader.Item("ClientNo"))
                Item.SubItems.Add(Reader.Item("ClientName"))
                Item.SubItems.Add(Reader.Item("EndorsType"))
                Item.SubItems.Add(Reader.Item("SavedUser"))
                Item.SubItems.Add(CDate(Reader.Item("SavedDate")).ToString("dd-MMM-yyyy HH:mm"))

                Me.ListPendingTrans.Items.Add(Item)
                X += 1
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillInsurCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurCompanies As ArrayList = GetInsurCompaniesList()

            Me.CombInsurComp.Items.Clear()

            For Each InsurComp As String In InsurCompanies
                Me.CombInsurComp.Items.Add(InsurComp)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    'Sub FillInsurClasses()
    '    Try
    '        Me.Cursor = Cursors.WaitCursor

    '        Dim InsurClasses As ArrayList = GetInsurClassList()

    '        Me.CombInsurClasses.Items.Clear()

    '        For Each InsurComp As String In InsurClasses
    '            Me.CombInsurClasses.Items.Add(InsurComp)
    '        Next

    '        Me.Cursor = Cursors.Default
    '    Catch ex As Exception
    '        Me.Cursor = Cursors.Default
    '        If cnn.State = ConnectionState.Open Then
    '            cnn.Close()
    '        End If
    '        MsgBox(ex.ToString)
    '    End Try
    'End Sub

    Sub Clear()
        Me.ErrProv.Clear()
        Me.txtClientNo.Clear()
        Me.txtClientName.Clear()
        Me.RDirectBusiness.Checked = True
        Me.CombDirectBusiness.SelectedIndex = -1
        Me.txtAccNo.Clear()
        Me.txtPolicyNo.Clear()
        Me.RNewIssue.Checked = True
        Me.CombEndorsType.SelectedIndex = -1
        Me.CombEndorsType.Enabled = False
        Me.txtEndorsNo.Enabled = False
        Me.txtEndorsNo.Clear()
        Me.CombInsurClasses.SelectedIndex = -1
        Me.CombInsurComp.SelectedIndex = -1
        Me.DTPIssueDate.Value = Today.Date
        Me.DTPPeriodFRm.Value = Today.Date

        Me.txtSumInsur.Value = 0
        Me.txtClientDNCNNo.Clear()
        Me.txtTotalPremium.Value = 0
        Me.txtNetPremium.Value = 0
        Me.txtCompDNCNNo.Clear()
        Me.txtCompComm.Value = 0

        Me.DTPPayDate.Value = Today.Date
        Me.txtPayAmount.Value = 0
        Me.GridPaymentsSchedule.Rows.Clear()

        Me.GridFiles.Rows.Clear()

        Me.txtAccNo.Focus()
    End Sub

    Function CheckPolicyNo(ByVal PolNo As String) As Boolean
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Policies Where PolicyNo=N'" & PolNo & "'", cnn)
            Dim Count As Integer

            cnn.Open()
            Count = CInt(cmd.ExecuteScalar)
            cnn.Close()

            If Count > 0 Then
                Me.Cursor = Cursors.Default
                Return True
            Else
                Me.Cursor = Cursors.Default
                Return False
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

#Region "Fill Policy Details"

    Sub FillPaymentSchedule()
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim cmd As New SqlCommand("Select * From PaymentsSchedule Where TransNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)
            Dim Reader As SqlDataReader

            Me.GridPaymentsSchedule.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.GridPaymentsSchedule.Rows.Add(New String() {CDate(Reader.Item("PayDate")).ToString("dd/MMM/yyyy"), _
                                                              CDbl(Reader.Item("Amount")).ToString("N2"), "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub FillDocuments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Dir As New IO.DirectoryInfo(FolderPath & "\" & Me.ListPendingTrans.SelectedItems(0).Text)
            Dim DirFiles As IO.FileInfo() = Dir.GetFiles()

            Me.GridFiles.Rows.Clear()

            ''list the names of all files in the specified directory
            For Each Fi As FileInfo In DirFiles
                Me.GridFiles.Rows.Add(New String() {Fi.FullName, Fi.Name, "Open", "Delete"})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

#End Region
    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.CombDirectBusiness.Items.Clear()
            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombDirectBusiness.Items.Add("Direct Business - " & Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillInsurClasses()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurClasses As ArrayList = GetInsurClassList()

            Me.CombInsurClasses.Items.Clear()

            For Each InsurClass As String In InsurClasses
                Me.CombInsurClasses.Items.Add(InsurClass)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmApprove_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillInsurCompanies()
        FillInsurClasses()
        FillPendingTransactions()
        FillBranches()

        'Set default dates
        Me.DTPPeriodTo.Value = Me.DTPPeriodFRm.Value.AddYears(1)
        Me.DTPPeriodTo.Value = Me.DTPPeriodTo.Value.AddDays(-1)
    End Sub

    Private Sub DTPPeriodFRm_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTPPeriodFRm.ValueChanged
        If Me.RNewIssue.Checked = True Then
            Me.DTPPeriodTo.Value = Me.DTPPeriodFRm.Value.AddYears(1)
            Me.DTPPeriodTo.Value = Me.DTPPeriodTo.Value.AddDays(-1)
        End If
    End Sub

    Private Sub DTPPeriodTo_Validating(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles DTPPeriodTo.Validating
        If Me.DTPPeriodFRm.Value > Me.DTPPeriodTo.Value Then
            MsgBox("Please select a valid date (policy period)")
            e.Cancel = True
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Try
            Me.ErrProv.Clear()
            If Me.txtPayAmount.Value = 0 Then
                Me.ErrProv.SetError(Me.txtPayAmount, "Please enter a valid amount")
                Me.txtPayAmount.Focus()
            Else
                Me.GridPaymentsSchedule.Rows.Add(New String() {Me.DTPPayDate.Value.ToString("dd/MMM/yyyy"), CDbl(Me.txtPayAmount.Value).ToString("N2"), "Delete"})
            End If

            Me.txtPayAmount.Value = 0
            Me.txtPayAmount.Focus()
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridPaymentsSchedule_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridPaymentsSchedule.CellClick
        If e.ColumnIndex = 2 Then
            If MsgBox("Confirm Delete?", MsgBoxStyle.YesNo, Me.Text) = MsgBoxResult.Yes Then
                Me.GridPaymentsSchedule.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub txtPolicyNo_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPolicyNo.Leave
        If Me.txtPolicyNo.Text.Trim.Length > 0 Then
            If CheckPolicyNo(Me.txtPolicyNo.Text.Trim) = True Then
                Me.ErrProv.SetError(Me.txtPolicyNo, "Policy no. already exists")
            Else
                Me.ErrProv.Clear()
            End If
        End If
    End Sub

    Private Sub txtPolicyNo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPolicyNo.TextChanged
        Me.ErrProv.Clear()
    End Sub

    Private Sub RNewIssue_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RNewIssue.CheckedChanged
        CheckRadioType()
    End Sub

    Sub CheckRadioType()
        If Me.RNewIssue.Checked = True Or Me.RRenewal.Checked = True Then
            Me.btnSelectPolicy.Visible = False
            Me.CombEndorsType.SelectedIndex = -1
            Me.CombEndorsType.Enabled = False
            Me.txtEndorsNo.Clear()
            Me.txtEndorsNo.Enabled = False
            Me.txtAccNo.ReadOnly = False
            Me.txtPolicyNo.ReadOnly = False
            Me.CombInsurComp.Enabled = True
            Me.CombInsurClasses.Enabled = True
            Me.DTPPeriodTo.Enabled = True

        ElseIf Me.REndors.Checked = True Then
            Me.btnSelectPolicy.Visible = True
            Me.CombEndorsType.Enabled = True
            Me.txtEndorsNo.Enabled = True
            Me.txtAccNo.ReadOnly = True
            Me.txtAccNo.Clear()
            Me.txtPolicyNo.ReadOnly = True
            Me.txtPolicyNo.Clear()
            Me.CombInsurComp.Enabled = False
            Me.CombInsurComp.SelectedIndex = -1
            Me.CombInsurClasses.Enabled = False
            Me.CombInsurClasses.SelectedIndex = -1
            Me.DTPPeriodTo.Enabled = False
        End If
    End Sub

    Sub FillPoliciesProducers(ByVal PolNo As Integer)
        Try

        Catch ex As Exception

        End Try
    End Sub

    Private Sub CombEndorsType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CombEndorsType.SelectedIndexChanged
        If Me.CombEndorsType.SelectedItem = "Extension" Then
            Me.DTPPeriodTo.Enabled = True
        Else
            Me.DTPPeriodTo.Enabled = False
        End If
    End Sub

    Private Sub txtEndorsNo_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtEndorsNo.Leave
        If Me.txtEndorsNo.Text.Trim.Length > 0 Then
            If CheckEndorsNo(Me.txtPolicyNo.Text.Trim, Me.txtEndorsNo.Text.Trim) = True Then
                Me.ErrProv.SetError(Me.txtEndorsNo, "Endorsement no. already exists")
            Else
                Me.ErrProv.Clear()
            End If
        End If
    End Sub

    Function CheckEndorsNo(ByVal PolicyNo As String, ByVal EndorsNo As String) As Boolean
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Policies Where PolicyNo=N'" & PolicyNo & "' and EndorsNo=N'" & EndorsNo & "' and Done=1", cnn)
            Dim Count As Integer

            cnn.Open()
            Count = CInt(cmd.ExecuteScalar)
            cnn.Close()

            If Count > 0 Then
                Me.Cursor = Cursors.Default
                Return True
            Else
                Me.Cursor = Cursors.Default
                Return False
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Private Sub REndors_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles REndors.CheckedChanged
        CheckRadioType()
    End Sub

    Private Sub btnSelectPolicy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectPolicy.Click
        Try
            Dim a As New frmSelectProdPolicy
            a.txtClientNo.Text = Me.txtClientNo.Text
            a.txtClientName.Text = Me.txtClientName.Text
            a.FillPolicies()
            a.ShowDialog()

            'Check if the user selected a policy
            If SelPolNo = "" Then
                Exit Sub
            Else
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Select * From Policies Where SNo=" & SelPolNo, cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While Reader.Read
                    Me.txtAccNo.Text = Reader.Item("AccNo")
                    Me.txtPolicyNo.Text = Reader.Item("PolicyNo")
                    Me.CombInsurComp.Text = Reader.Item("InsurComp")
                    Me.CombInsurClasses.Text = Reader.Item("ClassName")
                    Me.DTPPeriodTo.Value = CDate(Reader.Item("PeriodTo"))
                End While
                Reader.Close()
                cnn.Close()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RDirectBusiness_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RDirectBusiness.CheckedChanged
        CheckRadioProducer()
    End Sub

    Private Sub RProducer_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RProducer.CheckedChanged
        CheckRadioProducer()
    End Sub

    Private Sub RRenewal_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RRenewal.CheckedChanged
        CheckRadioType()
    End Sub

    Private Sub txtSumInsur_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSumInsur.Enter
        Me.txtSumInsur.Select(0, Me.txtSumInsur.Text.Length)
    End Sub

    Private Sub txtTotalPremium_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTotalPremium.Enter
        Me.txtTotalPremium.Select(0, Me.txtTotalPremium.Text.Length)
    End Sub

    Private Sub txtNetPremium_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtNetPremium.Enter
        Me.txtNetPremium.Select(0, Me.txtNetPremium.Text.Length)
    End Sub

    Private Sub txtPolicyNo_MouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles txtPolicyNo.MouseDoubleClick
        If Me.txtPolicyNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPolicyNo, "Please fill in")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim dap As New SqlDataAdapter("Select * From Policies Where PolicyNo=N'" & Me.txtPolicyNo.Text.Trim & "'", cnn)
                Dim das As New DataSet

                cnn.Open()
                dap.Fill(das, "Policies")
                cnn.Close()

                Dim rpt As New ListOfPolicies
                rpt.SetDataSource(das)
                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.CrystalReportViewer1.RefreshReport()
                rptViewer.ShowDialog()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnInsertDoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Try
            Me.OpenFileDialog1.Filter = "All files (*.*)|*.*"
            If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                Dim File As New FileInfo(Me.OpenFileDialog1.FileName)
                File.CopyTo(FolderPath & "\" & Me.ListPendingTrans.SelectedItems(0).Text & "\" & File.Name)

                FillDocuments()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub CheckRadioProducer()
        If Me.RDirectBusiness.Checked = True Then
            Me.CombDirectBusiness.Enabled = True
        ElseIf Me.RProducer.Checked = True Then
            Me.CombDirectBusiness.Enabled = False
            Me.CombDirectBusiness.SelectedIndex = -1
        End If
    End Sub

    Private Sub ListPendingTrans_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListPendingTrans.SelectedIndexChanged
        Try
            Clear()
            If Me.ListPendingTrans.SelectedItems.Count > 0 Then
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand("Select * From Policies Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text, cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While (Reader.Read)
                    'Check if Policy or Endorsement or Renewal
                    If Reader.Item("EndorsType") = "Policy" Then
                        Me.RNewIssue.Checked = True
                    Else
                        Me.REndors.Checked = True
                    End If
                    If Reader.Item("Renewal") = "Renewal" Then
                        Me.RRenewal.Checked = True
                    End If
                    Me.txtClientNo.Text = Reader.Item("ClientNo")
                    Me.txtClientName.Text = Reader.Item("ClientName")
                    Me.txtAccNo.Text = Reader.Item("AccNo")
                    Me.txtPolicyNo.Text = Reader.Item("PolicyNo")
                    Me.CombEndorsType.SelectedItem = Reader.Item("EndorsType")
                    Me.txtEndorsNo.Text = Reader.Item("EndorsNo")
                    Me.CombInsurComp.Text = Reader.Item("InsurComp")
                    Me.DTPIssueDate.Value = CDate(Reader.Item("IssueDate"))
                    Me.CombInsurClasses.Text = Reader.Item("ClassName")
                    Me.CombLineofBusiness.Text = Reader.Item("LineOfBusiness")
                    Me.DTPPeriodFRm.Value = CDate(Reader.Item("PeriodFrom"))
                    Me.DTPPeriodTo.Value = CDate(Reader.Item("PeriodTo"))
                    Me.txtSumInsur.Text = CDbl(Reader.Item("SumInsur")).ToString("N2")
                    Me.txtTotalPremium.Text = CDbl(Reader.Item("TotalPremium")).ToString("N2")
                    Me.txtNetPremium.Text = CDbl(Reader.Item("NetPremium")).ToString("N2")
                    Me.txtCompDNCNNo.Text = Reader.Item("CompCommDNCNNo")
                    Me.txtClientDNCNNo.Text = Reader.Item("ClientDNCNNo")
                    Me.txtCompComm.Value = Reader.Item("CompComm")
                End While
                Reader.Close()
                cnn.Close()

                FillPaymentSchedule()
                FillDocuments()

                Me.ListPendingTrans.Focus()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridFiles_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = Me.DGFilesColDocOpen.Index Then
            Try
                Me.Cursor = Cursors.WaitCursor

                System.Diagnostics.Process.Start(Me.GridFiles.CurrentRow.Cells(Me.DGFilesPath.Index).Value)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGFilesColDocDel.Index Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Me.GridFiles.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnApprove.Click
        Me.ErrProv.Clear()
        If Me.ListPendingTrans.SelectedItems.Count = 0 Then
            MsgBox("Please select a policy/endorsement from the list")
            Exit Sub

        ElseIf CurrentUser = Me.ListPendingTrans.SelectedItems(0).SubItems(3).Text Then
            MsgBox("You are not allowed to approve your own entries!")
            Exit Sub

        ElseIf Me.RDirectBusiness.Checked = True And Me.CombDirectBusiness.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombDirectBusiness, "Please select a valid producer from the list")

        ElseIf Me.txtAccNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAccNo, "Please fill in all details")
            Me.txtAccNo.Focus()

        ElseIf Me.txtPolicyNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPolicyNo, "Please fill in all details")
            Me.txtPolicyNo.Focus()

        ElseIf Me.REndors.Checked = True And Me.CombEndorsType.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombEndorsType, "Please fill in all details")
            Me.CombEndorsType.Focus()

        ElseIf Me.REndors.Checked = True And Me.txtEndorsNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtEndorsNo, "Please fill in all details")
            Me.txtEndorsNo.Focus()

        ElseIf Me.REndors.Checked = True And CheckEndorsNo(Me.txtPolicyNo.Text.Trim, Me.txtEndorsNo.Text.Trim) = True Then
            MsgBox("Endors. no. already exists in the same policy")
            Me.ErrProv.SetError(Me.txtEndorsNo, "Endors. no. already exists in the same policy")
            Me.txtEndorsNo.Focus()

        ElseIf Me.CombInsurComp.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombInsurComp, "Please fill in all details")
            Me.CombInsurComp.Focus()

        ElseIf Me.CombLineofBusiness.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombLineofBusiness, "Please fill in all details")
            Me.CombLineofBusiness.Focus()

        ElseIf Me.CombInsurClasses.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombInsurClasses, "Please fill in all details")
            Me.CombInsurClasses.Focus()

        ElseIf Me.txtCompComm.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtCompComm, "Please fill in all details")
            Me.txtCompComm.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                If Me.txtTotalPremium.Value = 0 Then
                    If MsgBox("Are you sure ""Total Premium"" is zero?", MsgBoxStyle.YesNo) = MsgBoxResult.No Then
                        Me.ErrProv.SetError(Me.txtTotalPremium, "Please fill in all details")
                        Me.txtTotalPremium.Focus()
                        Exit Sub
                    End If
                End If

                If Me.txtNetPremium.Value = 0 Then
                    If MsgBox("Are you sure ""Net. Premium"" is zero?", MsgBoxStyle.YesNo) = MsgBoxResult.No Then
                        Me.ErrProv.SetError(Me.txtNetPremium, "Please fill in all details")
                        Me.txtNetPremium.Focus()
                        Exit Sub
                    End If
                End If

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction
                Dim DocSNo, Year As Integer
                Dim IssueType, Abbreviation, Pattern, PatternType As String
                Dim Renewal As String = ""
                Dim SumInsur, TotPrem, NetPrem, CompComm As Double

                'Get Abbreviation
                Abbreviation = GetAbbreviation(Me.CombInsurClasses.SelectedItem)

                'Check entry type (Policy, Endorsement)
                If Me.RNewIssue.Checked = True Then
                    IssueType = "Policy"
                    PatternType = "POL"

                ElseIf Me.RRenewal.Checked = True Then
                    IssueType = "Policy"
                    Renewal = "Renewal"
                    PatternType = "POL"

                ElseIf Me.REndors.Checked = True Then
                    IssueType = Me.CombEndorsType.SelectedItem
                    PatternType = "END"
                End If

                If Me.CombEndorsType.SelectedItem = "Refund" Then
                    SumInsur = (-1) * CDbl(Me.txtSumInsur.Text)
                    TotPrem = (-1) * CDbl(Me.txtTotalPremium.Text)
                    NetPrem = (-1) * CDbl(Me.txtNetPremium.Text)
                    CompComm = (-1) * CDbl(Me.txtCompComm.Text)
                Else
                    SumInsur = CDbl(Me.txtSumInsur.Text)
                    TotPrem = CDbl(Me.txtTotalPremium.Text)
                    NetPrem = CDbl(Me.txtNetPremium.Text)
                    CompComm = CDbl(Me.txtCompComm.Text)
                End If

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Get Year
                cmd.CommandText = "Select Year(GetDate())"
                Year = CInt(cmd.ExecuteScalar)

                'Get Doc SNo
                If Me.RRenewal.Checked = True Or Me.RNewIssue.Checked = True Then
                    cmd.CommandText = "Select IsNull(Max(DocSNo),10000) From Policies Where Year(TransDate)=Year(GetDate()) and " & _
                                      "ClassName=@ClassName and EndorsType=N'Policy' and Branch=N'" & Branch & "'"
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@ClassName", Me.CombInsurClasses.Text)

                ElseIf Me.REndors.Checked = True Then
                    cmd.CommandText = "Select IsNull(Max(DocSNo),10000) From Policies Where Year(TransDate)=Year(GetDate()) and " & _
                                      "ClassName=@ClassName and EndorsType<>N'Policy' and Branch=N'" & Branch & "'"
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@ClassName", Me.CombInsurClasses.Text)
                End If
                DocSNo = CInt(cmd.ExecuteScalar) + 1

                'Create Pattern 
                Pattern = PatternType & "-" & BranchCode & "-" & Year & "-" & Abbreviation & "-" & DocSNo

                cmd.CommandText = "Update Policies Set Branch=@Branch,PolEndNo=@PolEndNo,DocSNo=@DocSNo," & _
                                   "ClientNo=@ClientNo,ClientName=@ClientName,AccNo=@AccNo,PolicyNo=@PolicyNo," & _
                                   "EndorsNo=@EndorsNo,EndorsType=@EndorsType,Renewal=@Renewal,InsurComp=@InsurComp," & _
                                   "IssueDate=@IssueDate,ClassName=@ClassName,LineOfBusiness=@LineOfBusiness," & _
                                   "PeriodFrom=@PeriodFrom,PeriodTo=@PeriodTo,SumInsur=@SumInsur,TotalPremium=@TotalPremium,NetPremium=@NetPremium," & _
                                   "CompComm=@CompComm,ClientDNCNNo=@ClientDNCNNo,ApprovedDate=GetDate(),ApprovedUser=@ApprovedUser,Done=1 " & _
                                   "Where SNo=" & Me.ListPendingTrans.SelectedItems(0).Text

                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@Branch", Branch)
                cmd.Parameters.AddWithValue("@PolEndNo", Pattern)
                cmd.Parameters.AddWithValue("@DocSNo", DocSNo)
                cmd.Parameters.AddWithValue("@ClientNo", Me.txtClientNo.Text.Trim)
                cmd.Parameters.AddWithValue("@ClientName", Me.txtClientName.Text.Trim)
                cmd.Parameters.AddWithValue("@AccNo", Me.txtAccNo.Text.Trim)
                cmd.Parameters.AddWithValue("@PolicyNo", Me.txtPolicyNo.Text.Trim)
                cmd.Parameters.AddWithValue("@EndorsNo", Me.txtEndorsNo.Text.Trim)
                cmd.Parameters.AddWithValue("@EndorsType", IssueType)
                cmd.Parameters.AddWithValue("@Renewal", Renewal)
                cmd.Parameters.AddWithValue("@InsurComp", Me.CombInsurComp.Text)
                cmd.Parameters.AddWithValue("@ClassName", Me.CombInsurClasses.Text)
                cmd.Parameters.AddWithValue("@LineOfBusiness", Me.CombLineofBusiness.Text)
                cmd.Parameters.AddWithValue("@IssueDate", Me.DTPIssueDate.Value)
                cmd.Parameters.AddWithValue("@PeriodFrom", Me.DTPPeriodFRm.Value)
                cmd.Parameters.AddWithValue("@PeriodTo", Me.DTPPeriodTo.Value)

                cmd.Parameters.AddWithValue("@SumInsur", SumInsur)
                cmd.Parameters.AddWithValue("@ClientDNCNNo", Me.txtClientDNCNNo.Text.Trim)
                cmd.Parameters.AddWithValue("@TotalPremium", TotPrem)
                cmd.Parameters.AddWithValue("@NetPremium", NetPrem)
                cmd.Parameters.AddWithValue("@CompCommDNCNNo", Me.txtCompDNCNNo.Text.Trim)
                cmd.Parameters.AddWithValue("@CompComm", CompComm)

                cmd.Parameters.AddWithValue("@ApprovedUser", CurrentUser)
                cmd.ExecuteNonQuery()


                'Delete The old Payments
                cmd.CommandText = "Delete From PaymentsSchedule Where TransNo=" & Me.ListPendingTrans.SelectedItems(0).Text
                cmd.ExecuteNonQuery()

                'insert the new data
                For Each row As DataGridViewRow In Me.GridPaymentsSchedule.Rows
                    cmd.CommandText = "Insert Into PaymentsSchedule (TransNo,PayDate,Amount,SavedUser,Department) Values " & _
                                      "(@TransNo,@PayDate,@Amount,@SavedUser,@Department)"

                    'Add Values
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@TransNo", Me.ListPendingTrans.SelectedItems(0).Text)
                    cmd.Parameters.AddWithValue("@PayDate", CDate(row.Cells(0).Value))
                    cmd.Parameters.AddWithValue("@Amount", CDbl(row.Cells(1).Value))
                    cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                    cmd.Parameters.AddWithValue("@Department", Branch)
                    cmd.ExecuteNonQuery()
                Next
                Trans.Commit()
                cnn.Close()

                MsgBox("Approved Successfully" & Chr(13) & "File No. " & Pattern)

                FillPendingTransactions()
                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnRefreshPendTrans_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefreshPendTrans.Click
        FillPendingTransactions()
    End Sub

    Private Sub CombInsurClasses_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CombInsurClasses.SelectedIndexChanged
        Me.CombLineofBusiness.Items.Clear()

        If Me.CombInsurClasses.SelectedIndex > -1 Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim LinesofBusinessList As ArrayList = GetLinesofBusinessList(Me.CombInsurClasses.Text)

                Me.CombLineofBusiness.Items.Clear()

                For Each LinesOfBusiness As String In LinesofBusinessList
                    Me.CombLineofBusiness.Items.Add(LinesOfBusiness)
                Next

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

End Class